package com.pocket.insight.base.mql.detector;

import com.google.common.base.Strings;
import com.pocket.insight.base.context.RelationToSqlContext;
import com.pocket.insight.base.mql.Conversion;
import com.pocket.insight.base.mql.TypedVal;
import com.pocket.insight.base.mql.relation.JoinRelation;
import com.pocket.insight.base.mql.relation.StatementRelation;
import com.pocket.insight.base.sql.Dialect;
import com.pocket.insight.base.sql.SqlEval;
import com.pocket.insight.base.sql.dialect.BaseDialect;
import com.pocket.insight.base.sql.dialect.Gbase8sDialect;
import com.pocket.insight.entity.ME;
import com.pocket.insight.entity.Type;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;

import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 描述：
 *
 * @author shuize
 * @create 2024/12/6
 */
public class SqlConversion implements Conversion {

    protected static String fromAliasPre = "_id_sub_from_";

    private Dialect dialect;

    public SqlConversion setDialect(Dialect dialect) {
        this.dialect = dialect;
        return this;
    }

    @Override
    public TypedVal bracketQueryWithAlias(TypedVal tableQuery, String alias) {
        if (tableQuery != null && tableQuery.getStr() != null) {
            String tableSql = tableQuery.getStr();
            if (!StringUtils.startsWithIgnoreCase(tableSql, "SELECT ") && !StringUtils.startsWithIgnoreCase(tableSql, "WITH ")) {
                return tableQuery;
            } else {
                tableSql = BaseDialect.bracketCustomSql(tableSql);
                tableSql = tableSql + " " + this.dialect.quoteTableName(alias);
                return new TypedVal(tableSql, Type.TypeName.dataset);
            }
        } else {
            return null;
        }
    }

    @Override
    public TypedVal statementQueryPack(QueryParts queryParts, StatementRelation statementRelation) {
        String sql;
        if (queryParts == null) {
            return null;
        }
        StringBuilder groupStr = new StringBuilder();
        String selectSql = selectToSql(queryParts.selectVal, queryParts.allAggConstant, groupStr, statementRelation);
        String sortSql = sortToSql(queryParts.sortVal, queryParts.valMap, statementRelation);

        if (BooleanUtils.isTrue(queryParts.allAggConstant)) {
            if (StringUtils.isBlank(sortSql)) {
                sql = this.dialect instanceof Gbase8sDialect ? selectSql + " FROM " + queryParts.from.getStr() : selectSql;
            } else {
                sql = selectSql + sortSql;
                if (queryParts.offset != 0L || queryParts.limit != null) {
                    sql = statementRelation.dialect.setPage(new TypedVal(sql), queryParts.offset, queryParts.limit).getStr();
                }
            }
        } else if (queryParts.from != null && queryParts.from.getStr() != null) {
            if (queryParts.whereVal != null) {
                String whereStr = queryParts.whereVal.getStr();
                if (this.dialect.boolToSqlLiteral(true).equals(whereStr)) {
                    queryParts.whereVal = null;
                }
            }

            //this.pullWhereDown(queryParts, statementRelation);
            sql = selectSql + " FROM " + queryParts.from.getStr() + (queryParts.whereVal == null ? "" : " WHERE " + queryParts.whereVal.getStr()) + groupStr;
            sql = sql + (queryParts.havingVal == null ? "" : " HAVING " + queryParts.havingVal.getStr()) + sortSql;
            if (queryParts.offset != 0L || queryParts.limit != null) {
                sql = statementRelation.dialect.setPage(new TypedVal(sql), queryParts.offset, queryParts.limit).getStr();
            }
        } else {
            sql = selectSql + statementRelation.dialect.getEmptyTable();
        }

        if (StringUtils.isNotBlank(queryParts.alias)) {
            if (sql.startsWith("SELECT ")) {
                sql = BaseDialect.bracketCustomSql(sql);
            }

            return new TypedVal(sql + " " + statementRelation.dialect.quoteTableName(queryParts.alias), Type.TypeName.dataset);
        } else {
            return new TypedVal(sql, Type.TypeName.dataset);
        }
    }

    /**
     * 构建 SQL 的 【SELECT field1, field2】 和 【GROUP BY field1, field2...】部分
     *
     * @param vals
     * @param allAggConstant
     * @param groupBy
     * @param statementRelation
     * @return
     */
    protected String selectToSql(List<TypedVal> vals, Boolean allAggConstant, StringBuilder groupBy, StatementRelation statementRelation) {
        // 1. 未指定字段，默认：*，返回：SELECT *
        if (CollectionUtils.isEmpty(vals)) {
            return "SELECT *";
        }
        // 2. 聚合模式 且 所有字段均在 group by 中，返回：SELECT field1, field2, field3 [empty table]
        String selectsql;
        if (allAggConstant) {
            Stream fieldAlias = vals.stream().map(val ->
                    val.getAlias() == null ? val.getStr() : statementRelation.dialect.fieldAsAlias(val.getStr(), statementRelation.dialect.quoteAliasName(val.getAlias()))
            );
            selectsql = "SELECT " + fieldAlias.collect(Collectors.joining(", "));
            selectsql = selectsql + statementRelation.dialect.getEmptyTable();
            return selectsql;
        }

        // 3. 聚合模式 且 所有字段不全在 group by 中，处理 GROUP BY，生成GROUP BY field2, field3 ...  || GROUP BY 1, 2 ...
        if (statementRelation.evalMode == SqlEval.EvalMode.EVAL_MODE_AGGREGATE) {
            if (vals.get(0).getStr().equals("*")) {
                vals.remove(0);
            }
            List<Integer> groups = new ArrayList();
            List<TypedVal> groupVals = new ArrayList();
            for (int i = 0; i < vals.size(); ++i) {
                if (vals.get(i).getScope() == TypedVal.EvalScope.row && !statementRelation.noGroupUid.contains((vals.get(i)).getAlias())) {
                    groups.add(i + 1);
                    groupVals.add(vals.get(i));
                }
            }
            if (statementRelation.dialect.canGroupByIndex() && !groups.isEmpty()) {
                groupBy.append(" GROUP BY ").append(StringUtils.join(groups, ", "));
            } else if (!statementRelation.dialect.canGroupByIndex() && !groupVals.isEmpty()) {
                // TODO MaxcomputeNativeDialect  需要特殊处理
                groupBy.append(" GROUP BY ").append(
                        groupVals.stream().map(val -> statementRelation.dialect.quoteGroupByFieldName(val)).collect(Collectors.joining(", "))
                );
            }
        }
        // 结果：SELECT field1, field2, field3...
        selectsql = vals.stream().map(val -> {
            if (StringUtils.isBlank(val.getAlias()) || (StringUtils.isNotBlank(val.getAlias()) && val.getStr().equals(statementRelation.dialect.quoteAliasName(val.getAlias())))) {
                return val.getStr();
            } else {
                return statementRelation.dialect.fieldAsAlias(val.getStr(), statementRelation.dialect.quoteAliasName(val.getAlias()));
            }
        }).collect(Collectors.joining(", "));
        return "SELECT " + selectsql;
    }

    /**
     * 构建 SQL 的 【ORDER BY 】部分
     *
     * @param sortVal
     * @param valMap
     * @param statementRelation
     * @return
     */
    protected String sortToSql(List<TypedVal> sortVal, Map<String, TypedVal> valMap, StatementRelation statementRelation) {
        if (CollectionUtils.isEmpty(sortVal)) {
            return "";
        }
        List<String> sortStrList = new ArrayList();
        List<String> orders = new ArrayList();
        for (int i = 0; i < sortVal.size(); i++) {
            TypedVal val = sortVal.get(i);
            ME expr = statementRelation.orderBy.get(i);
            boolean aliasInOrderBy = statementRelation.dialect.aliasInOrderBy();
            if (!statementRelation.isUnionSubRelation && statementRelation.from instanceof JoinRelation && statementRelation.dialect.isAliasAmbiguousInJoin()) {
                aliasInOrderBy = false;
            }
            if (val != null && (val.getScope() != TypedVal.EvalScope.constant || statementRelation.dialect.isOffsetLimitRequiresOrderBy() && (statementRelation.offset != 0L || statementRelation.limit != null))) {
                String sortStr;
                if (expr.kind == ME.Kind.reference) {
                    sortStr = aliasInOrderBy ? statementRelation.dialect.quoteOrderByAliasName(val) : val.getStr();
                } else {
                    sortStr = val.getStr();
                    if (valMap.containsKey(sortStr) && aliasInOrderBy) {
                        sortStr = statementRelation.dialect.quoteOrderByAliasName(valMap.get(sortStr));
                    }
                }

                if (!sortStrList.contains(sortStr)) {
                    sortStrList.add(sortStr);
                    orders.add(statementRelation.dialect.nullsOrder(sortStr, "ASC".equalsIgnoreCase(expr.direction) ? " ASC" : ("DESC".equalsIgnoreCase(expr.direction) ? " DESC" : " ASC"), val.getScope(), ME.NullsOrder.LAST.toString()));
                }
            }
        }

        List<String> result = orders.stream().filter(Objects::nonNull).distinct().collect(Collectors.toList());

        return result.isEmpty() ? "" : " ORDER BY " + String.join(", ", result);
    }

    @Override
    public TypedVal unionQueryPack(List<TypedVal> subQueries) {
        return new TypedVal(subQueries.stream().map(e -> e.getStr()).collect(Collectors.joining(" UNION ALL ")));
    }

    @Override
    public Pair<TypedVal, String> getQueryForReused(TypedVal query, String alias, String fromAlias, List<String> withUids, Boolean noNeedToReuse, Integer accessCount) {
        if (query != null && query.getStr() != null) {
            String backupSql = query.getStr();
            if ((noNeedToReuse || accessCount <= 1) && !StringUtils.startsWithIgnoreCase(backupSql, "with ")) {
                return new ImmutablePair(query, null);
            } else if ((fromAlias == null || withUids != null) && !withUids.contains(fromAlias)) {
                if (backupSql.startsWith("(")) {
                    String var10001 = this.dialect.quoteAliasName(fromAlias);
                    if (backupSql.endsWith(") " + var10001)) {
                        query.setStr(query.getStr().substring(1, query.getStr().length() - this.dialect.quoteAliasName(fromAlias).length() - 2));
                    }
                }
                if ((!this.dialect.supportWith() || !StringUtils.startsWithIgnoreCase(query.getStr(), "select ")) && !StringUtils.startsWithIgnoreCase(query.getStr(), "with ")) {
                    query.setStr(backupSql);
                    return new ImmutablePair(query, (Object)null);
                } else {
                    if (Strings.isNullOrEmpty(alias)) {
                        alias = fromAlias == null ? fromAliasPre + RelationToSqlContext.getRewriteIndex().getAndIncrement() : fromAlias;
                    }
                    if (StringUtils.startsWithIgnoreCase(query.getStr(), "with ")) {
                        if (!StringUtils.startsWithIgnoreCase(query.getStr(), "with " + this.dialect.getLeftQuoteChar() + alias + this.dialect.getRightQuoteChar() + " AS (")) {
                            return new ImmutablePair(query, null);
                        }
                        query.setStr(query.getStr().substring("WITH `` AS (".length() + alias.length(), query.getStr().length() - 1));
                    }
                    return new ImmutablePair(query, alias);
                }
            } else {
                return new ImmutablePair(query, null);
            }
        } else {
            return new ImmutablePair(query, null);
        }
    }


    public enum TypeName {
        bool,
        integer,
        number,
        latitude,
        longitude,
        time,
        date,
        country_name,
        country_code,
        country_alpha_2,
        country_alpha_3,
        china_province_name,
        china_province_code,
        china_province_pinyin,
        china_city_name,
        china_city_code,
        china_county_name,
        china_county_code,
        string,
        json,
        array,
        string_array,
        number_array,
        integer_array,
        date_array,
        time_array,
        object_id,
        any,
        unknown,
        dataset;

        private Type self;

        public Type type() {
            if (this.self == null) {
                // this.self = Type.fromTypeName(this);
            }
            return this.self;
        }
    }
}
